package com.yupi.springbootinit.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.util.*;

/**
 * Excel处理工具类
 * 修复了当Excel列标题为空时导致SQL语法错误的问题
 */
@Slf4j
public class ExcelUtils {
    
    /**
     * 将Excel文件转换为CSV格式字符串
     * 
     * @param multipartFile 上传的Excel文件
     * @return CSV格式字符串
     */
    public static String excelToCsv(MultipartFile multipartFile) {
        // 读取Excel文件
        File file = null;
        try {
            file = File.createTempFile("excel_", ".tmp");
            multipartFile.transferTo(file);
            ExcelReader reader = ExcelUtil.getReader(file);
            
            // 读取所有行数据
            List<List<Object>> rows = reader.read(0);
            
            if (CollUtil.isEmpty(rows)) {
                return "";
            }
            
            // 处理表头，确保列名不为空
            List<Object> headerRow = rows.get(0);
            List<String> headers = new ArrayList<>();
            Set<String> usedHeaders = new HashSet<>();
            
            for (int i = 0; i < headerRow.size(); i++) {
                String originalHeader = Objects.toString(headerRow.get(i), "").trim();
                String columnName;
                
                // 如果原始列名为空，则生成默认列名
                if (StringUtils.isBlank(originalHeader)) {
                    columnName = "column_" + (i + 1);
                } else {
                    // 确保列名是有效的标识符，替换非法字符
                    columnName = originalHeader.replaceAll("[^a-zA-Z0-9_\\u4e00-\\u9fa5]", "_");
                    // 如果处理后的列名为空或以数字开头，则添加前缀
                    if (columnName.isEmpty() || Character.isDigit(columnName.charAt(0))) {
                        columnName = "col_" + columnName;
                    }
                }
                
                // 处理重复的列名
                String finalColumnName = columnName;
                int suffix = 1;
                while (usedHeaders.contains(finalColumnName)) {
                    finalColumnName = columnName + "_" + suffix;
                    suffix++;
                }
                
                headers.add(finalColumnName);
                usedHeaders.add(finalColumnName);
            }
            
            // 构建CSV内容
            StringBuilder csvContent = new StringBuilder();
            
            // 添加处理后的表头
            csvContent.append(String.join(",", headers)).append("\n");
            
            // 添加数据行
            for (int i = 1; i < rows.size(); i++) {
                List<Object> dataRow = rows.get(i);
                List<String> dataStrings = new ArrayList<>();
                
                for (Object cell : dataRow) {
                    String cellString = Objects.toString(cell, "");
                    // 转义CSV中的特殊字符
                    if (cellString.contains(",") || cellString.contains("\"") || cellString.contains("\n")) {
                        cellString = "\"" + cellString.replace("\"", "\"\"") + "\"";
                    }
                    dataStrings.add(cellString);
                }
                
                csvContent.append(String.join(",", dataStrings)).append("\n");
            }
            
            return csvContent.toString();
        } catch (IOException e) {
            log.error("处理Excel文件失败", e);
            throw new RuntimeException("处理Excel文件失败", e);
        } finally {
            if (file != null && file.exists()) {
                FileUtil.del(file);
            }
        }
    }
}